package com.yidu.poi;

import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.forwardedUrl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * 类的描述-- 基于excel导入 导出
 * @author 楠竹
 * @date 2018年8月1日
 * @version 1.0
 */
public class PoiUtil {

	static Logger logger = Logger.getLogger(PoiUtil.class);

	public static void main(String[] args) throws IOException {
		//export();
		/*	
		File file = new File("e:\\1.xls"); 
		String fileName = file.getName();
		if(fileName.endsWith(".xls")){
			System.out.println(1);
		}else if(fileName.endsWith(".xlsx")){
			System.out.println(0);
		}
		System.out.println(fileName);*/
		importFile();
	}

	/**
	 * 基于excel 导出
	 * @throws IOException 
	 */
	public static void export() throws IOException{

		String[] attr  = {"姓名","班级","成绩"};

		//创建HSSFWorkbook对象(excel的文档对象)
		HSSFWorkbook wb = new HSSFWorkbook();

		//建立新的sheet对象（excel的表单）
		HSSFSheet sheet=wb.createSheet("成绩表");

		//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
		HSSFRow row1=sheet.createRow(0);

		//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
		HSSFCell cell=row1.createCell(0);

		//设置单元格内容
		cell.setCellValue("学员考试成绩一览表");

		//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

		//在sheet里创建第二行
		HSSFRow row2 = sheet.createRow(1);
		for (int i = 0; i < attr.length; i++) {
			//创建单元格并设置单元格内容
			row2.createCell(i).setCellValue(attr[i]); 

		}

		for(int i=2;i<10;i++){
			//在sheet增加多行
			HSSFRow rows = sheet.createRow(i);
			for(int j=0;j<attr.length;j++){
				rows.createCell(j).setCellValue(i+" "+j);

			}
		}


		//导出excel
		OutputStream os = new FileOutputStream("e:\\1.xls");

		wb.write(os);
		os.close();

		System.out.println("完成！");


	}

	/**
	 * excel 导入
	 * @throws IOException 
	 */
	public static void importFile() throws IOException{

		List<Map<String, Object>>  list = new ArrayList<>();
		//得到excel文件
		File file = new File("e:\\1.xls"); 

		//得到文件名
		String fileName = file.getName();
		//得到流
		InputStream is = new FileInputStream(file);
		//判断后缀
		Workbook wb   =null;
		//根据指定的文件输入流导入Excel从而产生Workbook对象
		if(fileName.endsWith(".xls")){

			wb = new HSSFWorkbook(is);
		}else if(fileName.endsWith(".xlsx")){

			wb = new XSSFWorkbook(is);
		} 

		/*//获取Excel文档中的第一个表单
		Sheet sht = wb.getSheetAt(0);*/


		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		//遍历excel 所有表单
		for(int i=0;i<wb.getNumberOfSheets();i++){
			//得到单个表单
			 sheet = wb.getSheetAt(i);

			if(sheet == null) continue;

			//取到第二行标题
			 row = sheet.getRow(1);
			String[] title = null;

			//String[] ss = new String[5];
			//判断是否存在
			if(row !=null){
				//得到该行的单元格数
				title = new String[row.getLastCellNum()];

				for(int j = row.getFirstCellNum();j<row.getLastCellNum();j++){
					
					//得到该行的列
					cell = row.getCell(j);
					//将列的值赋值给title
					title[j] = cell.getStringCellValue();
					
				} 
			}else continue;
			
			logger.info(new ObjectMapper().writeValueAsString(title));
			
			
			
			//遍历当前sheet中的所有行
			
			for(int k = 2;k<sheet.getLastRowNum()+1;k++){
				row = sheet.getRow(k);
				Map<String, Object> map = new HashMap<>();
				
				for(int z = row.getFirstCellNum();z<row.getLastCellNum();z++){
					cell = row.getCell(z);
					String key  = title[z];
					
					map.put(key, cell.getStringCellValue());
				}
				
				list.add(map);
			}
			
		}

		logger.info(new ObjectMapper().writeValueAsString(list));
		wb.close();
		




	}
}
